<?php
/**
 * Excel 导入
 * XN
 */
namespace app\voip\controller;
use app\index\controller\Company;
use app\voip\func\ChildrenFunc;
use app\voip\func\CompanyFunc;
use app\voip\func\CorrelationFunc;
use app\voip\func\CTypeFunc;
use app\voip\func\DataTypeFunc;
use app\voip\func\DataUseFunc;
use app\voip\func\ExcelListFunc;
use app\voip\func\GradeFunc;
use app\voip\func\GradePupilFunc;
use app\voip\func\HomeAddressFunc;
use app\voip\func\HomeTypeFunc;
use app\voip\func\JobTypeFunc;
use app\voip\func\PatriarchFunc;
use app\voip\func\ProvinceFunc;
use app\voip\func\SchoolFunc;
use app\voip\func\ThFunc;
use app\voip\func\WorksheetFunc;
use app\voip\model\EGradePupil;
use excel\Excel as ExcelAlias;
use function PHPSTORM_META\type;
use think\Db;
use vae\controller\AdminCheckAuth;

class ExcelLeadController extends AdminCheckAuth
{
    protected $validateName='ExcelLoad';
    public function index()
    {
        $dType=DataTypeFunc::all();
        return view('',['typeList'=>$dType]);
    }

    /**
     * Excel上传并返回路径
     */
    public function excelUpload()
    {
        // 获取表单上传文件
        try{
            $file = request()->file('file');
            list($floder,$path)=getExcelUploadFileName();
            $info = $file->validate(['size'=>1024*1024*5,'ext'=>'doc,docx,xml,csv,xlsx'])->move($path);
            if ($info){
                $filePath=$floder.DS.$info->getSaveName();
                $pathName=$info->getPathname();
            }else{
                throw new \Exception($info->getError());
            }
            return json_encode(['status'=>1,'filePath'=>$filePath,'pathName'=>$pathName,'msg'=>'upload ok']);
        }catch (\Exception $e) {
            return json_encode(['status'=>0,'msg'=>$e->getMessage()]);
        }

    }
    /**
     * 数据上传
     */
    public function excelUpLoadAndMysqlSave()
    {
        try{
            Db::startTrans();
            $validate=validate($this->validateName);
            if (!$validate->scene(__FUNCTION__)->check(input())){
                throw new \Exception($validate->getError());
            }
            $filePath=input('filePath');
            /*** 数据保存 Excle*/
            $aid = vae_get_login_admin('id');

            /*** $eid excelID*/
            $eid=ExcelListFunc::add(
                $aid,input('title'),
                md5(input('download_password')),
                $filePath,input('type_id'),
                input('data_intro')
                ,input('remark'),
                'E'.date("YmdHis").uniqid()
            );

            $province = input('province',"默认");
            $city = input('city',"默认");
            $county = input('county',"默认");
            $school_name= input('school_name',"默认");
            $th_name= input('th_name',"默认");
            $class= input('class',"默认");
            /**
             * $patriarchArray 家长 $childrenArray 学生
             */

            list($patriarchArray,$childrenArray)=$this->getExcelToArray(input('pathName'));
            for ($x=0;$x<4;$x++){
                unset($patriarchArray[$x]);
                unset($childrenArray[$x]);
            }
            switch ((int)input('import_type'))
            {
                case 1:
                    /*** 对应*/
                    $this->excelMapping($patriarchArray,$childrenArray,$eid,$province,$city ,$county, $school_name ,$th_name ,$class);
                    break;
                case 2:
                    /***家长*/
                    $this->excelPatriarch($patriarchArray, $eid,$province,$city ,$county, $school_name ,$th_name ,$class);
                    break;
                case 3:
                    /*** 孩子*/
                    $this->excelChildren( $childrenArray, $eid,$province,$city ,$county, $school_name ,$th_name ,$class);

                    break;
            }
            Db::commit();
            return json_encode(['status'=>1,'msg'=>'upload ok']);
        }catch (\Exception $e){
            dump($e->getMessage());
            Db::rollback();
            return json_encode(['status'=>0,'msg'=>$e->getMessage()]);
        }

    }


    /**
     * 家长信息导入
     * @param $patriarchArray
     * @param $eid
     * @param $province
     * @param $city
     * @param $county
     * @param $school_name
     * @param $th_name
     * @param $class
     * @throws \think\exception\DbException
     */
    private function excelPatriarch($patriarchArray,$eid,$province,$city ,$county, $school_name ,$th_name ,$class)
    {
        $index=0;
        $correlationsTypeName=$plist=$eth=$clist=$homeAddrc=$homeAddrp=$provincec=
        $worksheetsTypeName= $provincep=$homeAddrpType=$gradesPupil=$homeAddrcType=
        $schools=$grades=$correlations=$worksheets=$companyS=$ssq=$pdataType=[];

        foreach ($patriarchArray as $key=>$val)
        {
            if (empty($val[0])){continue;}
            if ($index>=1000){
                $this->excelPatriarchDataAdd($ssq, $plist, $homeAddrpType, $worksheetsTypeName,$homeAddrp,$worksheets,$companyS,$pdataType);
                $correlationsTypeName=$plist=$eth=$clist=$homeAddrc=$homeAddrp=$provincec=
                $worksheetsTypeName= $provincep=$homeAddrpType=$gradesPupil=$homeAddrcType=
                $schools=$grades=$correlations=$worksheets=$companyS=$pdataType=$ssq=[];
                $index=0;
            }




            /** @var TYPE_NAME $plist 家长基础信息数据
            [
            'name'=>$patriarchArray[$key][0], 'sex'=>$patriarchArray[$key][1], 'age'=>strtotime($patriarchArray[$key][2]),
            'phone'=>$patriarchArray[$key][10], 'wechat'=>$patriarchArray[$key][11], 'qq'=>$patriarchArray[$key][12],
            'email'=>$patriarchArray[$key][13], 'alipay_number'=>$patriarchArray[$key][14],
            'interest'=>$patriarchArray[$key][9], 'remark'=>$patriarchArray[$key][20],'eid'=>$eid,
            ]
             */

            $plist[]=$this->getPatriarchInfo($val,$eid);

            $result=$this->getParameterType($val,15);
            $pdataType[$result['name']]=$result['name'];


            /** @var TYPE_NAME $homeAddrpType 家长住址类型 'type_name'=>$patriarchArray[$key][16], */

            $result=$this->getParameterType($val,2,$province,$city ,$county, $school_name ,$th_name ,$class);
            $homeAddrpType[$result['type_name']]=$result['type_name'];




            /** @var TYPE_NAME $worksheets 工作表
            [
            'intro'=>$patriarchArray[$key][4], 'job_id'=>'',
            'ymoney'=>$patriarchArray[$key][6], 'disburse'=>$patriarchArray[$key][7],
            'up_id'=>'',
            ]
             */
            $worksheets[]=$this->getParameterType($val,8);
            /** 工作类型
            [
            'type_name'=>$patriarchArray[$key][5],
            ]
             */
            $result=$this->getParameterType($val,9);
            $worksheetsTypeName[$result['type_name']]=$result['type_name'];

            /** @var TYPE_NAME $companyS  公司*/
            $result=$this->getParameterType($patriarchArray[$key],14);
            $companyS[$result['company_name']]=$result['company_name'];


            $homeAddrp[]=$this->getParameterType($val,11,$province,$city ,$county, $school_name ,$th_name ,$class);

            $result=$this->getParameterType($val,13,$province,$city ,$county, $school_name ,$th_name ,$class);
            $provinces[]=$result;
            $ssq=$this->ssq($ssq,$result);
        }
        $this->excelPatriarchDataAdd($ssq, $plist, $homeAddrpType, $worksheetsTypeName,$homeAddrp,$worksheets,$companyS,$pdataType);
    }

    /**
     * @param $plist
     * @param $homeAddrpType
     * @param $worksheetsTypeName
     * @param $homeAddrp
     * @param $worksheets
     * @throws \think\exception\DbException
     * @throws \Exception
     */
    private function excelPatriarchDataAdd($ssq, $plist, $homeAddrpType, $worksheetsTypeName,$homeAddrp,$worksheets,$companyS,$pdataType)
    {
        $ssq=ProvinceFunc::checkSave($ssq);

        $plistData=$plist;
        $pdataType=$this->excelDataType($pdataType);
        foreach ($plistData as $key=>$val){
            $plist[$key]['etype_id']=$pdataType[$val['etype_id']];
        }

        $presult=$this->addPatriarchAll($plist);//家长基本信息


        $companyS=$this->companyDataCheckUniqid($companyS);


        $homeAddrpType=$this->homeDataCheckUniqid($homeAddrpType);



        $worksheetsTypeName=$this->worksheetsTypeDataCheckUniqid($worksheetsTypeName);


        /**
         * ======================================================数据整理
         * @var  $key
         * @var  $val
         */
        foreach ($presult as $key=>$val){
            $pid=$val->getData('id');//家长ID
            $pTypeId=$homeAddrpType[$homeAddrp[$key]['ht_id']];//家长住址类型ID

            $jobTypeId=$worksheetsTypeName[$worksheets[$key]['job_id']];//工作类型Id

            /*** 住址*/
            $homeAddrp[$key]['pid']=$pid;
            $homeAddrp[$key]['ht_id']=$pTypeId;

            $provinceId=$this->forSsq($ssq,$homeAddrp[$key]['province'],$homeAddrp[$key]['city'],$homeAddrp[$key]['county'],0);
            $cityId=$this->forSsq($ssq,$homeAddrp[$key]['province'],$homeAddrp[$key]['city'],$homeAddrp[$key]['county'],1);

            $countyId=$this->forSsq($ssq,$homeAddrp[$key]['province'],$homeAddrp[$key]['city'],$homeAddrp[$key]['county'],2);
            $homeAddrp[$key]['province']=$provinceId;
            $homeAddrp[$key]['city']=$cityId;
            $homeAddrp[$key]['county']=$countyId;

            /** 工作职业 */
            $worksheets[$key]['up_id']=$pid;
            $worksheets[$key]['job_id']=$jobTypeId;
            $worksheets[$key]['company_id']=$companyS[$worksheets[$key]['company_id']];
            /** 工作职业 */

        }

        HomeAddressFunc::addAll($homeAddrp);
        WorksheetFunc::addAll($worksheets);

    }

    /**
     * @param $data
     * @return mixed
     * @throws \think\exception\DbException
     */
    private function excelDataType($data)
    {
        return DataUseFunc::checkSave($data);
    }
    /**
     * 娃娃信息导入
     * @param $childrenArray
     * @param $eid
     * @param $province
     * @param $city
     * @param $county
     * @param $school_name
     * @param $th_name
     * @param $class
     * @throws \think\exception\DbException
     */
    private function excelChildren($childrenArray,$eid,$province,$city ,$county, $school_name ,$th_name ,$class)
    {
        $index=0;
        $correlationsTypeName=$plist=$eth=$clist=$homeAddrc=$homeAddrp=$provincec=
        $worksheetsTypeName= $provincep=$homeAddrpType=$gradesPupil=$homeAddrcType=
        $schools=$grades=$correlations=$worksheets=$cdataType=$companyS=$ssq=[];

        foreach ($childrenArray as $key=>$val)
        {
            if (empty($val[0])){continue;}
            if ($index>=1000){
                $this->excelChildrenDataAdd($ssq, $clist, $homeAddrcType, $schools, $eth, $homeAddrc,$grades,$cdataType);

                $correlationsTypeName=$plist=$eth=$clist=$homeAddrc=$homeAddrp=$provincec=
                $worksheetsTypeName= $provincep=$homeAddrpType=$gradesPupil=$homeAddrcType=
                $schools=$grades=$correlations=$worksheets=$companyS=$cdataType=$ssq=[];
                $index=0;
            }


            /** @var TYPE_NAME $clist 娃娃基础信息数据*/
            $clist[]=$this->getChildInfo($val,$eid);
            $result=$this->getParameterType($val,16);
            $cdataType[$result['name']]=$result['name'];

            /** @var TYPE_NAME $homeAddrcType 娃娃住址类型 */
            $result=$this->getParameterType($val,1,$province,$city ,$county, $school_name ,$th_name ,$class);
            $homeAddrcType[$result['type_name']]=$result['type_name'];



            /** @var TYPE_NAME $schools 学校 ['name'=>$val[3]]*/

            $result=$this->getParameterType($val,3,$province,$city ,$county, $school_name ,$th_name ,$class);
            $schools[$result['name']]=$result['name'];

            /** @var TYPE_NAME $eth 第几届 'th_name'=>$val[4]*/
            $result=$this->getParameterType($val,4,$province,$city ,$county, $school_name ,$th_name ,$class);
            $eth[$result['th_name']]=$result['th_name'];

            /** @var TYPE_NAME $grades 班级
            [
            'sid'=>'', 'name'=>$val[5],
            'money'=>$val[6], 'remark'=>'',
            'th_id'=>'',//第几届
            ]
             */
            $grades[]=$this->getParameterType($val,5,$province,$city ,$county, $school_name ,$th_name ,$class);

            $homeAddrc[]=$this->getParameterType($val,10,$province,$city ,$county, $school_name ,$th_name ,$class);

            /** @var TYPE_NAME $provinces 省市区 */
            $result=$this->getParameterType($val,12,$province,$city ,$county, $school_name ,$th_name ,$class);
            $ssq=$this->ssq($ssq,$result);


        }


        $this->excelChildrenDataAdd($ssq, $clist, $homeAddrcType, $schools, $eth, $homeAddrc,$grades,$cdataType);

    }


    /**
     * @param $ssq
     * @param $clist
     * @param $homeAddrcType
     * @param $schools
     * @param $eth
     * @param $homeAddrc
     * @throws \think\exception\DbException
     */
    private function excelChildrenDataAdd($ssq, $clist, $homeAddrcType, $schools, $eth, $homeAddrc,$grades,$cdataType)
    {
        $ssq=ProvinceFunc::checkSave($ssq);


        $clistData=$clist;
        $cdataType=$this->excelDataType($cdataType);
        foreach ($clistData as $key=>$val){
            $clist[$key]['etype_id']=$cdataType[$val['etype_id']];
        }
        $cresult=$this->addChildAll($clist);//娃娃基本信息
        $gradesUniqid=$gradesPupil=[];

        $homeAddrcType=$this->homeDataCheckUniqid($homeAddrcType);

        $schools=$this->schoolsDataCheckUniqid($schools);

        $eth=$this->ethDataCheckUniqid($eth);


        /**
         * ======================================================数据整理
         * @var  $key
         * @var  $val
         */
        foreach ($cresult as $key=>$val){
            $cid=$val->getData('id');//娃ID
            $ctypeId=$homeAddrcType[$homeAddrc[$key]['ht_id']];//娃娃住址类型ID

            /*** 住址*/
            $homeAddrc[$key]['cid']=$cid;
            $homeAddrc[$key]['ht_id']=$ctypeId;

            $provinceId=$this->forSsq($ssq,$homeAddrc[$key]['province'],$homeAddrc[$key]['city'],$homeAddrc[$key]['county'],0);
            $cityId=$this->forSsq($ssq,$homeAddrc[$key]['province'],$homeAddrc[$key]['city'],$homeAddrc[$key]['county'],1);
            $countyId=$this->forSsq($ssq,$homeAddrc[$key]['province'],$homeAddrc[$key]['city'],$homeAddrc[$key]['county'],2);
            $homeAddrc[$key]['province']=$provinceId;
            $homeAddrc[$key]['city']=$cityId;
            $homeAddrc[$key]['county']=$countyId;

            /** 娃娃 班级*/

            $grades[$key]['sid']=$schools[$grades[$key]['sid']];
            $grades[$key]['th_id']=$eth[$grades[$key]['th_id']];

            /** 班级与学生关系  */
            $uniqid=md5($grades[$key]['sid'].$grades[$key]['th_id'].$grades[$key]['name'].$grades[$key]['money']);
            /** @var TYPE_NAME $gradesUniqid  唯一班级*/
            $gradesUniqid[$uniqid]=$this->gradeUniqid($grades[$key],$uniqid);

            $gradesPupil[$key]['cid']=$cid;
            $gradesPupil[$key]['gid']=$uniqid;
        }
        HomeAddressFunc::addAll($homeAddrc);
        $graderesult=GradeFunc::checkSave($gradesUniqid);
        //班级与学生的关系
        foreach ($gradesPupil as $key=>$val)
        {
            $gradesPupil[$key]['gid']=$graderesult[$val['gid']];
        }
        GradePupilFunc::addAll($gradesPupil);
    }

    /**
     * @param $patriarchArray
     * @param $childrenArray
     * @param $eid
     * @param $province
     * @param $city
     * @param $county
     * @param $school_name
     * @param $th_name
     * @param $class
     * @throws \think\exception\DbException
     */
    private function excelMapping($patriarchArray, $childrenArray, $eid,$province,$city ,$county, $school_name ,$th_name ,$class)
    {
        $index=0;
        $correlationsTypeName=$plist=$eth=$clist=$homeAddrc=$homeAddrp=$provincec=
        $worksheetsTypeName= $provincep=$homeAddrpType=$gradesPupil=$homeAddrcType=
        $schools=$grades=$correlations=$worksheets=$pdataType=$cdataType=$companyS=$ssq=[];

        foreach ($childrenArray as $key=>$val)
        {
            if (empty($val[0])){continue;}
            if ($index>=1000){
                $this->mappingDataAdd($clist, $plist, $homeAddrcType, $homeAddrpType, $correlationsTypeName, $worksheetsTypeName,
                    $schools, $eth, $homeAddrc, $homeAddrp,$correlations,$worksheets,$grades,$ssq,$companyS,$pdataType,$cdataType);

                $correlationsTypeName=$plist=$eth=$clist=$homeAddrc=$homeAddrp=$provincec=
                $worksheetsTypeName= $provincep=$homeAddrpType=$gradesPupil=$homeAddrcType=
                $schools=$grades=$correlations=$worksheets=$pdataType=$cdataType=$companyS=$ssq=[];
                $index=0;
            }


            /** @var TYPE_NAME $clist 娃娃基础信息数据*/
            $clist[]=$this->getChildInfo($val,$eid);
            $result=$this->getParameterType($val,16);
            $cdataType[$result['name']]=$result['name'];

            /** @var TYPE_NAME $plist 家长基础信息数据
            [
            'name'=>$patriarchArray[$key][0], 'sex'=>$patriarchArray[$key][1], 'age'=>strtotime($patriarchArray[$key][2]),
            'phone'=>$patriarchArray[$key][10], 'wechat'=>$patriarchArray[$key][11], 'qq'=>$patriarchArray[$key][12],
            'email'=>$patriarchArray[$key][13], 'alipay_number'=>$patriarchArray[$key][14],
            'interest'=>$patriarchArray[$key][9], 'remark'=>$patriarchArray[$key][20],'eid'=>$eid,
            ]
             */

            $plist[]=$this->getPatriarchInfo($patriarchArray[$key],$eid);
            $result=$this->getParameterType($patriarchArray[$key],16);
            $pdataType[$result['name']]=$result['name'];

            /** @var TYPE_NAME $homeAddrcType 娃娃住址类型 */
            $result=$this->getParameterType($val,1,$province,$city ,$county, $school_name ,$th_name ,$class);
            $homeAddrcType[$result['type_name']]=$result['type_name'];

            /** @var TYPE_NAME $homeAddrpType 家长住址类型 'type_name'=>$patriarchArray[$key][16], */
            $result=$this->getParameterType($patriarchArray[$key],2,$province,$city ,$county, $school_name ,$th_name ,$class);
            $homeAddrpType[$result['type_name']]=$result['type_name'];

            /** @var TYPE_NAME $schools 学校 ['name'=>$val[3]]*/

            $result=$this->getParameterType($val,3,$province,$city ,$county, $school_name ,$th_name ,$class);
            $schools[$result['name']]=$result['name'];

            /** @var TYPE_NAME $eth 第几届 'th_name'=>$val[4]*/
            $result=$this->getParameterType($val,4,$province,$city ,$county, $school_name ,$th_name ,$class);
            $eth[$result['th_name']]=$result['th_name'];

            /** @var TYPE_NAME $grades 班级
             [
            'sid'=>'', 'name'=>$val[5],
            'money'=>$val[6], 'remark'=>'',
            'th_id'=>'',//第几届
            ]
             */
            $grades[]=$this->getParameterType($val,5,$province,$city ,$county, $school_name ,$th_name ,$class);




            /** @var TYPE_NAME $correlations 关系表
              [
            'pid'=>'',
            'cid'=>'',
            'ctype'=>'',
            'cname'=>$patriarchArray[$key][3]
            ]
             */
            $correlations[]=$this->getParameterType($patriarchArray[$key],6);

            /** @var TYPE_NAME $correlationsTypeName  关系 类型名
             [
            'type_name'=>$patriarchArray[$key][3]
            ]
             */
            $result=$this->getParameterType($patriarchArray[$key],7);
            $correlationsTypeName[$result['type_name']]=$result['type_name'];


            /** @var TYPE_NAME $worksheets 工作表
            [
            'intro'=>$patriarchArray[$key][4], 'job_id'=>'',
            'ymoney'=>$patriarchArray[$key][6], 'disburse'=>$patriarchArray[$key][7],
            'up_id'=>'',
            ]
             */
            $worksheets[]=$this->getParameterType($patriarchArray[$key],8);
            /** 工作类型
            [
            'type_name'=>$patriarchArray[$key][5],
            ]
             */
            $result=$this->getParameterType($patriarchArray[$key],9);
            $worksheetsTypeName[$result['type_name']]=$result['type_name'];

            /** @var TYPE_NAME $companyS  公司*/
            $result=$this->getParameterType($patriarchArray[$key],14);
            $companyS[$result['company_name']]=$result['company_name'];



            $homeAddrc[]=$this->getParameterType($val,10,$province,$city ,$county, $school_name ,$th_name ,$class);
            $homeAddrp[]=$this->getParameterType($patriarchArray[$key],11,$province,$city ,$county, $school_name ,$th_name ,$class);

            /** @var TYPE_NAME $provinces 省市区 */
            $result=$this->getParameterType($val,12,$province,$city ,$county, $school_name ,$th_name ,$class);

            $ssq=$this->ssq($ssq,$result);
            $result=$this->getParameterType($patriarchArray[$key],13,$province,$city ,$county, $school_name ,$th_name ,$class);
            $ssq=$this->ssq($ssq,$result);
        }

        $this->mappingDataAdd($clist, $plist, $homeAddrcType, $homeAddrpType, $correlationsTypeName, $worksheetsTypeName,
           $schools, $eth, $homeAddrc, $homeAddrp,$correlations,$worksheets,$grades,$ssq,$companyS,$pdataType,$cdataType);
    }


    /**
     * @param $ssq
     * @param $result
     * @return mixed
     */
    private function ssq($ssq, $result)
    {
        if (empty($ssq[$result['province']])){
                $ssq[$result['province']]=[$result['province']];
        }
        if (empty($ssq[$result['province']][$result['city']])) {
            $ssq[$result['province']][$result['city']] = [$result['city']];
        }
        if (empty($ssq[$result['province']][$result['city']][$result['county']])){
            $ssq[$result['province']][$result['city']][$result['county']]=$result['county'];
        }
        return $ssq;
    }

    /**
     * @param $clist
     * @param $plist
     * @param $homeAddrcType
     * @param $homeAddrpType
     * @param $correlationsTypeName
     * @param $worksheetsTypeName
     * @param $schools
     * @param $eth
     * @param $homeAddrc
     * @param $homeAddrp
     * @param $correlations
     * @param $worksheets
     * @param $grades
     * @param $ssq
     * @param $companyS
     * @throws \think\exception\DbException
     */
    private function mappingDataAdd($clist, $plist, $homeAddrcType, $homeAddrpType, $correlationsTypeName, $worksheetsTypeName, $schools, $eth, $homeAddrc, $homeAddrp,$correlations,$worksheets,$grades,$ssq,$companyS,$pdataType,$cdataType)
    {
        $ssq=ProvinceFunc::checkSave($ssq);
        $gradesUniqid=$gradesPupil=[];

        $pdataType=$this->excelDataType($pdataType);
        $cdataType=$this->excelDataType($cdataType);

        $plistData=$plist;
        $clistData=$clist;
        foreach ($plistData as $key=>$val){
            $plist[$key]['etype_id']=$pdataType[$val['etype_id']];
            $clist[$key]['etype_id']=$cdataType[$clist[$key]['etype_id']];
        }


        $cresult=$this->addChildAll($clist);//娃娃基本信息
        $presult=$this->addPatriarchAll($plist);//家长基本信息

        $homeAddrcType=$this->homeDataCheckUniqid($homeAddrcType);

        $homeAddrpType=$this->homeDataCheckUniqid($homeAddrpType);

        $correlationsTypeName=$this->cTypeDataCheckUniqid($correlationsTypeName);

        $companyS=$this->companyDataCheckUniqid($companyS);

        $worksheetsTypeName=$this->worksheetsTypeDataCheckUniqid($worksheetsTypeName);

        $schools=$this->schoolsDataCheckUniqid($schools);

        $eth=$this->ethDataCheckUniqid($eth);


        /**
         * ======================================================数据整理
         * @var  $key
         * @var  $val
         */
        foreach ($cresult as $key=>$val){
            $pid=$presult[$key]->getData('id');//家长ID
            $cid=$val->getData('id');//娃ID
            $pTypeId=$homeAddrpType[$homeAddrp[$key]['ht_id']];//家长住址类型ID
            $ctypeId=$homeAddrcType[$homeAddrc[$key]['ht_id']];//娃娃住址类型ID
            $jobTypeId=$worksheetsTypeName[$worksheets[$key]['job_id']];//工作类型Id

            /*** 住址*/
            $homeAddrc[$key]['cid']=$cid;
            $homeAddrc[$key]['ht_id']=$ctypeId;

            $provinceId=$this->forSsq($ssq,$homeAddrc[$key]['province'],$homeAddrc[$key]['city'],$homeAddrc[$key]['county'],0);
            $cityId=$this->forSsq($ssq,$homeAddrc[$key]['province'],$homeAddrc[$key]['city'],$homeAddrc[$key]['county'],1);
            $countyId=$this->forSsq($ssq,$homeAddrc[$key]['province'],$homeAddrc[$key]['city'],$homeAddrc[$key]['county'],2);
            $homeAddrc[$key]['province']=$provinceId;
            $homeAddrc[$key]['city']=$cityId;
            $homeAddrc[$key]['county']=$countyId;

            $homeAddrp[$key]['pid']=$pid;
            $homeAddrp[$key]['ht_id']=$pTypeId;

            $provinceId=$this->forSsq($ssq,$homeAddrp[$key]['province'],$homeAddrp[$key]['city'],$homeAddrp[$key]['county'],0);
            $cityId=$this->forSsq($ssq,$homeAddrp[$key]['province'],$homeAddrp[$key]['city'],$homeAddrp[$key]['county'],1);

            $countyId=$this->forSsq($ssq,$homeAddrp[$key]['province'],$homeAddrp[$key]['city'],$homeAddrp[$key]['county'],2);
            $homeAddrp[$key]['province']=$provinceId;
            $homeAddrp[$key]['city']=$cityId;
            $homeAddrp[$key]['county']=$countyId;

            /** 关系表 **/
            $correlations[$key]['pid']=$pid;
            $correlations[$key]['cid']=$cid;
            $correlations[$key]['ctype']=$correlationsTypeName[$correlations[$key]['ctype']];
            /** 工作职业 */
            $worksheets[$key]['up_id']=$pid;
            $worksheets[$key]['job_id']=$jobTypeId;
            $worksheets[$key]['company_id']=$companyS[$worksheets[$key]['company_id']];
            /** 娃娃 班级*/

            $grades[$key]['sid']=$schools[$grades[$key]['sid']];
            $grades[$key]['th_id']=$eth[$grades[$key]['th_id']];

            /** 班级与学生关系  */
            $uniqid=md5($grades[$key]['sid'].$grades[$key]['th_id'].$grades[$key]['name'].$grades[$key]['money']);
            /** @var TYPE_NAME $gradesUniqid  唯一班级*/
            $gradesUniqid[$uniqid]=$this->gradeUniqid($grades[$key],$uniqid);

            $gradesPupil[$key]['cid']=$cid;
            $gradesPupil[$key]['gid']=$uniqid;
        }
        HomeAddressFunc::addAll($homeAddrc);
        HomeAddressFunc::addAll($homeAddrp);
        CorrelationFunc::addAll($correlations);
        WorksheetFunc::addAll($worksheets);
        $graderesult=GradeFunc::checkSave($gradesUniqid);
        //班级与学生的关系
        foreach ($gradesPupil as $key=>$val)
        {
            $gradesPupil[$key]['gid']=$graderesult[$val['gid']];
        }
        GradePupilFunc::addAll($gradesPupil);
    }

    /**
     * @param $grades
     * @param $uniqid
     * @return array
     */
    private function gradeUniqid($grades, $uniqid)
    {
        return [
            'sid'=>$grades['sid'],
            'th_id'=>$grades['th_id'],
            'name'=>$grades['name'],
            'money'=>$grades['money'],
            'uniqid'=>$uniqid,
            'remark'=>$grades['remark'] ?? 'Excel导入',
        ];
    }
    /**
     * 循环查找出省市区的ID
     * @param $ssq
     * @param $province
     * @param $city
     * @param $county
     * @param $type
     * @return int|string
     */
    private function forSsq($ssq, $province,$city,$county,$type)
    {
        $id=0;
        foreach ($ssq as $key=>$val){
            if ($key==$province && $type==0){
                $id=$val[0];
                break;
            }
            if ($key==$province && $type==1){
                $id=$val[$city][0];
                break;
            }
            if ($key==$province && $type==2){
                $id=$val[$city][$county];
                break;
            }
        }
        return $id;
    }
    /**
     * 住址信息类型重新整理
     * @param $data
     * @return mixed
     * @throws \think\exception\DbException
     */
    private function homeDataCheckUniqid($data)
    {
        $homeTypeResult=HomeTypeFunc::checkSave($data);//去重后的数据 ID
        return $homeTypeResult;
    }


    /**
     * 公司
     * @param $data
     * @return mixed
     * @throws \think\exception\DbException
     */
    private function companyDataCheckUniqid($data)
    {


        $resultData=CompanyFunc::checkSave($data);//去重后的数据 ID

        return $resultData;
    }
    /**
     * 关系信息重新整理
     * @param $data
     * @return mixed
     * @throws \think\exception\DbException
     */
    private function cTypeDataCheckUniqid($data)
    {

        $result=CTypeFunc::checkSave($data);//去重后的数据 ID

        return $result;
    }
    /**
     * 工作类型信息重新整理
     * @param $data
     * @return mixed
     * @throws \think\exception\DbException
     */
    private function worksheetsTypeDataCheckUniqid($data)
    {

        $work=JobTypeFunc::checkSave($data);//去重后的数据 ID

        return $work;
    }
    /**
     * 学校信息重新整理
     * @param $data
     * @return mixed
     * @throws \think\exception\DbException
     */
    private function schoolsDataCheckUniqid($data)
    {


        $result=SchoolFunc::checkSave($data);//去重后的数据 ID


        return $result;
    }
    /**
     * 第几届信息重新整理
     * @param $data
     * @return mixed
     * @throws \think\exception\DbException
     */
    private function ethDataCheckUniqid($data)
    {


        $result=ThFunc::checkSave($data);//去重后的数据 ID


        return $result;
    }
    /**
     * @param $data
     * @return array|false
     * @throws \Exception
     */
    private function addChildAll($data)
    {
        return ChildrenFunc::addAll($data);//娃娃基本信息
    }

    /**
     * @param $data
     * @return array|false
     * @throws \Exception
     */
    private function addPatriarchAll($data)
    {
        return PatriarchFunc::addAll($data);//家长基本信息
    }

    /**
     * @param $val
     * @param $index
     * @param string $province
     * @param string $city
     * @param string $county
     * @param string $school_name
     * @param string $th_name
     * @param string $class
     * @return 娃娃住址类型|家长住址类型|学校名字|第几届|班级|身份关系表|关系类名|工作|工作类型|娃娃住址|家长住址
     */
    private function getParameterType($val, $index,$province='',$city='' ,$county='', $school_name ='',$th_name='' ,$class='')
    {
        $result=[];
        switch ($index)
        {
            case 1:
                /** @var  $result  娃娃住址类型*/
                $result=['type_name'=>$val[14]];
                break;
            case 2:
                /** @var  $result 家长住址类型*/
                $result=['type_name'=>$val[17]];
                break;
            case 3:
                /** @var  $result 学校名字 */
                $result=['name'=>$val[3] ?? $school_name];
                break;
            case 4:
                /** @var  $result  第几届*/
                $result=['th_name'=>$val[4] ?? $th_name ];
                break;
            case 5:
                /** @var $result 班级*/
                $result=[
                    'sid'=>$val[3], 'name'=>$val[5] ?? $class,
                    'money'=>$val[6], 'remark'=>'',
                    'th_id'=>$val[4],//第几届
                ];
                break;
            case 6:
                /** @var $result 关系表*/
                $result=[
                    'pid'=>'',
                    'cid'=>'',
                    'ctype'=>$val[3],
                    'cname'=>$val[3]
                ];
                break;
            case 7:

                /** @var TYPE_NAME $result 关系类名*/
                $result=[
                    'type_name'=>$val[3]
                ];
                break;
            case 8:
                /** @var  $result 工作*/
                $result=[
                    'intro'=>$val[5], 'job_id'=>$val[6],
                    'ymoney'=>$val[7], 'disburse'=>$val[8],
                    'up_id'=>'','level_id'=>$val[9],
                    'company_id'=>$val[4],
                ];
                break;
            case 9:
                /** @var  $result 工作类型*/
                $result=['type_name'=>$val[6]];
                break;
            case 10:
                $result=[
                    'province'=>$val[15] ?? $province,
                    'city'=>$val[16] ?? $city,
                    'county'=>$val[17] ?? $county,
                    'detailed_addr'=>$val[13],
                    'ht_id'=>$val[14],
                    'type'=>1,
                    'remark'=>'Excel导入'
                ];
                break;
            case 11:
                $result=[
                    'province'=>$val[18] ?? $province,
                    'city'=>$val[19] ?? $city,
                    'county'=>$val[20] ?? $county,
                    'detailed_addr'=>$val[16],
                    'ht_id'=>$val[17],
                    'type'=>2,
                    'remark'=>'Excel导入'
                ];
                break;
            case 12:
                $result=[
                    'province'=>$val[15] ?? $province,
                    'city'=>$val[16] ?? $city,
                    'county'=>$val[17] ?? $county,
                ];
                break;
            case 13:
                $result=[
                    'province'=>$val[18] ?? $province,
                    'city'=>$val[19] ?? $city,
                    'county'=>$val[20] ?? $county
                ];
                break;
            case 14:
                $result=[
                    'company_name'=>$val[4] ?? "默认"
                ];
                break;
            case 15:
                /**
                 * 第一张数据来源
                 */
                $result=[
                    'name'=>$val[21] ?? "默认"
                ];
                break;
            case 16:
                /**
                 * 第二张数据来源
                 */
                $result=[
                    'name'=>$val[20] ?? "默认"
                ];
                break;
        }

        return $result;
    }
    /**
     * @param $val
     * @param $eid
     * @return array
     */
    private function getChildInfo($val, $eid)
    {

            return [
                'name'=>$val[0], 'sex'=>$val[1], 'age'=>strtotime($val[2]),
                'phone'=>$val[8], 'wechat'=>$val[9], 'qq'=>$val[10], 'email'=>$val[11],
                'alipay_number'=>$val[12],
                'interest'=>$val[7],'etype_id'=>$val[20] ?? "默认" ,'remark'=>$val[21] ?? 'Excel导入','eid'=>$eid,
            ];
    }
    /**
     * @param $val
     * @param $eid
     * @return array
     */
    private function getPatriarchInfo($val, $eid)
    {

        return [
            'name'=>$val[0], 'sex'=>$val[1], 'age'=>strtotime($val[2]),
            'phone'=>$val[11], 'wechat'=>$val[12], 'qq'=>$val[13],
            'email'=>$val[14], 'alipay_number'=>$val[15],
            'interest'=>$val[10],'etype_id'=>$val[21] ?? "默认", 'remark'=>$val[22] ?? 'Excel 导入','eid'=>$eid,
        ];;
    }
    /**
     * 获取Excel数据
     * @param $uploadfile
     * @return array
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    protected function getExcelToArray($uploadfile)
    {
        /**
         * 家长表 数组 下标 4开始
         * 数据格式 key=>val 下面数据下标 4 开始 依次类推
         * 【
         * 姓名 性别 生日 职业介绍 职业类别 年薪 年消费水准 消费判断 兴趣
         * 手机号 微信号 QQ号 邮箱 支付宝账号
         * 住址 住址类别 省 市 区 备注
         * 】
         */
        $patriarchArray = ExcelAlias::excelReader($uploadfile,0);
        /**
         * 孩子 数组 下标 4开始
         * 数据格式 key=>val 下面数据下标 4 开始 依次类推
         * 【
         * 姓名 性别 生日 学校/机构 班级 班级费用 兴趣
         * 手机号 微信号 QQ号 邮箱 支付宝账号
         * 住址 住址类别 省 市 区
         * 消费情况 备注 家长Excel序列号（序列号-1 =真实数字下标）
         * 】
         */
        $childrenArray = ExcelAlias::excelReader($uploadfile,1);

        return [$patriarchArray,$childrenArray];
    }
}